package dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import user.Grade;
import util.DruidUtils;

import java.sql.SQLException;
import java.util.List;

public class GradeDaoImp implements GradeDao {
    @Override
    public int addGrade(Grade grade) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="INSERT INTO homework.grade values(?,?,?,?,?,?)";
        Object[] params = {grade.getNum(),grade.getName(),grade.getYear(),grade.getTeacher(),grade.getSlogan(), grade.getPeopleNum()};
        return qr.update(sql,params);
    }

    @Override
    public int deleteGrade(String num) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="delete from homework.grade where num= ? ";
        return qr.update(sql,num);
    }

    @Override
    public int motifyGrade(String num, Grade grade) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="update homework.grade SET `name`=?,`year`=?,`teacher`=?,`slogan`=?,`peopleNum`=? where `num`=?";
        Object[] params = {grade.getName(),grade.getYear(),grade.getTeacher(),grade.getSlogan(),grade.getPeopleNum(),grade.getNum()};
        return qr.update(sql,params);
    }

    @Override
    public Grade findGrade(String num) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="select * from homework.grade where num=?";
        return qr.query(sql,new BeanHandler<Grade>(Grade.class),num);
    }

    @Override
    public int countGrade() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="select count(num) FROM homework.grade";
        return ((Long) qr.query(sql, new ScalarHandler())).intValue();
    }

    @Override
    public List<Grade> getGrade(String start, String end) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="select * from homework.grade ORDER BY num ASC LIMIT ?,?";
        int i = Integer.parseInt(start);
        int i1 = Integer.parseInt(end);
        Object[] params = {i*i1,i1};
        return qr.query(sql,new BeanListHandler<Grade>(Grade.class),params);
    }

    @Override
    public int countStudent(String num) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="SELECT COUNT(g.`num`)FROM homework.grade g INNER JOIN homework.student s ON g.`num`=s.`gradeId` WHERE g.`num`=?";
        return ((Long) qr.query(sql, new ScalarHandler(),num)).intValue();
    }

    @Override
    public Grade findGradeByName(String name) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="select * from homework.grade where name=?";
        return qr.query(sql,new BeanHandler<Grade>(Grade.class),name);
    }
}
